import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 * MySQL数据库连接操作
 */
public class Test3 {
    public static void main(String[] args) throws Exception {
        test2(); //添加数据方法
        test1(); //打印user对象方法
        //test3(); //修改数据方法
        //test4(); //删除数据方法
    }

    /**
     * 查询所有数据
     *
     * @throws Exception
     */
    public static void test1() throws Exception {
        Properties prop = new Properties();
        prop.load(new FileInputStream("JDBC\\src\\druid.properties"));

        //获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);

        //获取数据库连接
        Connection conn = dataSource.getConnection();

        //准备sql
        String sql = "select * from users;";

        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //执行sql
        ResultSet rs = pstmt.executeQuery();

        List<User> list = new ArrayList<>();
        //封装User对象到集合里面
        while (rs.next()) {
            int id = rs.getInt(1);
            String name = rs.getString(2);
            int age = rs.getInt(3);
            String status = rs.getString(4);
            String gender = rs.getString(5);
            int def_id = rs.getInt(6);
            int managerid = rs.getInt(7);
            int money = rs.getInt(8);
            String date = rs.getString(9);

            User u = new User();
            u.setId(id);
            u.setAge(age);
            u.setName(name);
            u.setStatus(status);
            u.setGender(gender);
            u.setDefId(def_id);
            u.setManagerid(managerid);
            u.setMoney(money);
            u.setEntryDate(date);

            list.add(u);
        }

        //释放资源
        rs.close();
        pstmt.close();
        conn.close();

        System.out.println("查询数据库的全部信息如下：");
        for (User user : list) {
            System.out.println(user);
        }
    }

    /**
     * 增加数据
     *
     * @throws Exception
     */
    public static void test2() throws Exception {
        Properties prop = new Properties();
        prop.load(new FileInputStream("JDBC\\src\\druid.properties"));

        //获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);

        //获取数据库连接
        Connection conn = dataSource.getConnection();

        //准备sql
        String sql = "insert into users(name, age, status, gender, def_id, managerid, money, entrydate)\n" +
                "values (?,?,?,?,?,?,?,?)";


        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置参数
        String n1 = "tim";
        int n2 = 21;
        String n3 = "0";
        String n4 = "女";
        int n5 = 5;
        int n6 = 2;
        int n7 = 5200;
        String n8 = "2024-1-31";

        pstmt.setString(1, n1);
        pstmt.setInt(2, n2);
        pstmt.setString(3, n3);
        pstmt.setString(4, n4);
        pstmt.setInt(5, n5);
        pstmt.setInt(6, n6);
        pstmt.setInt(7, n7);
        pstmt.setString(8, n8);

        //执行sql
        int rs = pstmt.executeUpdate();

        if (rs > 0) {
            System.out.println("数据添加成功");
        } else {
            System.out.println("数据添加失败");

            //释放资源
            pstmt.close();
            conn.close();
        }
    }

    /**
     * 修改数据
     *
     * @throws Exception
     */
    public static void test3() throws Exception {
        Properties prop = new Properties();
        prop.load(new FileInputStream("JDBC\\src\\druid.properties"));

        //获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);

        //获取数据库连接
        Connection conn = dataSource.getConnection();

        //准备sql
        String sql = "update users set name = ?, age = ? , status = ?, gender = ? , def_id = ?, managerid = ? , money = ?, entrydate = ? where id = ?;";


        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置参数
        int id = 18;
        String n1 = "liubi";
        int n2 = 21;
        String n3 = "0";
        String n4 = "女";
        int n5 = 5;
        int n6 = 2;
        int n7 = 5200;
        String n8 = "2024-1-31";

        pstmt.setString(1, n1);
        pstmt.setInt(2, n2);
        pstmt.setString(3, n3);
        pstmt.setString(4, n4);
        pstmt.setInt(5, n5);
        pstmt.setInt(6, n6);
        pstmt.setInt(7, n7);
        pstmt.setString(8, n8);
        pstmt.setInt(9, id);


        //执行sql
        int rs = pstmt.executeUpdate();

        if (rs > 0) {
            System.out.println("修改成功");
        } else {
            System.out.println("修改失败");

            //释放资源
            pstmt.close();
            conn.close();
        }
    }

    /**
     * 删除数据
     *
     * @throws Exception
     */
    public static void test4() throws Exception {
        Properties prop = new Properties();
        prop.load(new FileInputStream("JDBC\\src\\druid.properties"));

        //获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);

        //获取数据库连接
        Connection conn = dataSource.getConnection();

        //准备sql
        String sql = "delete from users where id = ?;";


        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置参数
        int id = 17;

        pstmt.setInt(1, id);


        //执行sql
        int rs = pstmt.executeUpdate();

        if (rs > 0) {
            System.out.println("删除成功");
        } else {
            System.out.println("删除失败");

            //释放资源
            pstmt.close();
            conn.close();
        }
    }
}
